package db.document;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import o.进货单据类型;
import db.Database;
import db.history.SystemLogDB;
import db.history.经营情况DB;
import po.AccountBookPo;
import po.DocumentPo;
import po.Po;
import po.SystemLogPo;
import po.商品列表单项po;
import po.进退货单po;

public class ImportAndReturnDocumentDB extends DocumentDB{

	
	
	
    public ImportAndReturnDocumentDB(AccountBookPo ab) throws SQLException {
		super(ab);
		// TODO Auto-generated constructor stub
		String createT_Import_Export="CREATE TABLE IF NOT EXISTS `seac2`.`"+ab.getName()+"_import_export` (`f_id` varchar(20) NOT NULL COMMENT '单据编号',`f_type` int(1) NOT NULL DEFAULT '0' COMMENT '单据类型',`f_supplyer` varchar(20) NOT NULL COMMENT '供应商', `f_repertory` varchar(20) NOT NULL COMMENT '仓库',`f_operator` varchar(20) NOT NULL COMMENT '操作员',`f_item` int(4) NOT NULL AUTO_INCREMENT COMMENT '入库商品列表', `f_total` bigint(8) NOT NULL COMMENT '总额', `f_remark` varchar(50) NOT NULL COMMENT '备注', `f_isApproved` tinyint(1) NOT NULL DEFAULT '0' COMMENT '已审批', `f_date` date NOT NULL, PRIMARY KEY (`f_item`), UNIQUE KEY `f_id` (`f_id`), UNIQUE KEY `f_item` (`f_item`)) ENGINE=InnoDB DEFAULT CHARSET=utf8";
		String createT_Import_Export_item="CREATE TABLE IF NOT EXISTS `seac2`.`"+ab.getName()+"_import_export_item` (`f_key` int(4) NOT NULL, `f_commodityID` varchar(20) NOT NULL COMMENT '商品编号', `f_commodityName` varchar(20) NOT NULL COMMENT '商品名称', `f_commodityModel` varchar(20) NOT NULL COMMENT '商品型号', `f_commodityNum` int(4) NOT NULL COMMENT '数量', `f_commodityUnitPrice` bigint(8) NOT NULL COMMENT '单价', `f_total` bigint(8) NOT NULL COMMENT '总价', `f_remark` varchar(50) NOT NULL COMMENT '备注', `f_id` int(4) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`f_id`),UNIQUE KEY `f_id` (`f_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8";
		Connection c = Database.getConnection();
		Statement s = c.createStatement();
		s.execute(createT_Import_Export);
		s.execute(createT_Import_Export_item);
		s.close();
		
	  }
	
	  
	  
	  
	  
	@Override
	public Po add(DocumentPo po) {
		List<商品列表单项po> list=((进退货单po) po).get入库商品列表();
		String supplyer=((进退货单po) po).get客户();
		String repertory=((进退货单po) po).get仓库();
		String operator=((进退货单po) po).get操作员();
		long total=((进退货单po) po).get总额();
		String remark=((进退货单po) po).get备注()==null? "":((进退货单po) po).get备注();
		String date=new SimpleDateFormat("yyyy-MM-dd").format(new Date());int type;String r_id;
		int total_num=0;boolean num_isValid=true;
		Po m=new Po();
		if(supplyer==null||repertory==null){
			m.failed("请完整单据！");
			return m;
		}
		Connection c=Database.getConnection();
		Statement s;
		try {
			int key=0;
			s = c.createStatement();
			ResultSet rs=s.executeQuery("select * from "+book.getName()+"_import_export");
			while(rs.next()){
				total_num++;
			}
			if(((进退货单po) po).get类型()==进货单据类型.进货单){
				type=0;
				r_id=getDocumentId("JHD",total_num);
			}else{
				type=1;
				r_id=getDocumentId("JHTHD",total_num);
			}
			if(r_id.contains("JHTHD")){
				//查看库存数量
				for(int i=0;i<list.size();i++){
					ResultSet rs2=s.executeQuery("Select f_stockNum from "+book.getName()+"_commodity where f_commodityID='"+list.get(i).get商品编号()+"'");
					int Num=0;
					while(rs2.next()){
					 Num=rs2.getInt(1);
					}
					if(list.get(i).get数量()>Num){
						num_isValid=false;
						m.failed("库存中的"+list.get(i).get商品名称()+"数量小于您填写的进货退货数量！");
						return m;
					}
				}
				
			}
			if(num_isValid==true){
			s.executeUpdate("insert into "+book.getName()+"_import_export(f_id,f_type,f_supplyer,f_repertory,f_operator,f_total,f_remark,f_date) values('"+r_id+"','"+type+"','"+supplyer+"','"+repertory+"','"+operator+"','"+total+"','"+remark+"','"+date+"');");
			ResultSet r=s.executeQuery("select f_item from "+book.getName()+"_import_export where f_id='"+r_id+"'");
			while(r.next()){
		    key=r.getInt("f_item");
			}
			for(int i=0;i<list.size();i++){
		    String re=list.get(i).get备注()==null? "":list.get(i).get备注();
			s.executeUpdate("insert into "+book.getName()+"_import_export_item(f_key,f_commodityID,f_commodityName,f_commodityModel,f_commodityNum,f_commodityUnitPrice,f_total,f_remark) values('"+key+"','"+list.get(i).get商品编号()+"','"+list.get(i).get商品名称()+"','"+list.get(i).get商品型号()+"','"+list.get(i).get数量()+"','"+list.get(i).get单价()+"','"+list.get(i).get金额()+"','"+re+"');");
			}
			} 
			s.close();
			m.successed();
			SystemLogDB log=new SystemLogDB();
			SystemLogDB.iniSystemLogDB();
			log.add(new SystemLogPo(po.get操作员(),"add",r_id));
		} catch (SQLException e) {
			m.failed(e.getMessage());
			e.printStackTrace();
		}
		return m;
	}

	

	@Override
	public Po update(DocumentPo po) {
		String doc_id=((进退货单po) po).get单据编号();
		List<商品列表单项po> list=((进退货单po) po).get入库商品列表();
		String supplyer=((进退货单po) po).get客户();
		String repertory=((进退货单po) po).get仓库();
		String operator=((进退货单po) po).get操作员();
		String remark=((进退货单po) po).get备注();
		Po m=new Po();
		Connection c=Database.getConnection();
		Statement s;
		try {
			int key=0;
			s = c.createStatement();
			if(supplyer!=null){
				s.addBatch("update "+book.getName()+"_import_export set f_supplyer='"+supplyer+"' where f_id='"+doc_id+"'");
			}
			if(repertory!=null){
				s.addBatch("update "+book.getName()+"_import_export set f_repertory='"+repertory+"' where f_id='"+doc_id+"'");
			}
			if(operator!=null){
				s.addBatch("update "+book.getName()+"_import_export set f_operator='"+operator+"' where f_id='"+doc_id+"'");
			}
			if(remark!=null){
				s.addBatch("update "+book.getName()+"_import_export set f_remark='"+remark+"' where f_id='"+doc_id+"'");
			}
			
			ResultSet r=s.executeQuery("select f_item from "+book.getName()+"_import_export where f_id='"+doc_id+"'");
			while(r.next()){
		    key=r.getInt("f_item");
			}
			s.addBatch("delete from "+book.getName()+"_import_export_item where f_key='"+key+"'");
			for(int i=0;i<list.size();i++){
			s.addBatch("insert into "+book.getName()+"_import_export_item(f_key,f_commodityID,f_commodityName,f_commodityModel,f_commodityNum,f_commodityUnitPrice,f_total,f_remark) values('"+key+"','"+list.get(i).get商品编号()+"','"+list.get(i).get商品名称()+"','"+list.get(i).get商品型号()+"','"+list.get(i).get数量()+"','"+list.get(i).get单价()+"','"+list.get(i).get金额()+"','"+list.get(i).get备注()+"');");
			
			}
			s.executeBatch();
			s.close();
			m.successed();
		} catch (SQLException e) {
			m.failed(e.getMessage());
			e.printStackTrace();
		}
		return m;
	}

	@Override
	public List<DocumentPo> getUnapproval() {
		List<DocumentPo> list=new ArrayList<DocumentPo>();
		Connection c=Database.getConnection();
		Statement s;Statement s2;
		try {
			s = c.createStatement();
			ResultSet rs=s.executeQuery("select * from "+book.getName()+"_import_export where f_isApproved='"+0+"'");
			while(rs.next()){
				进退货单po po=new 进退货单po();
				po.set单据编号(rs.getString("f_id"));
				po.set类型(rs.getInt("f_type")==0? 进货单据类型.进货单:进货单据类型.进货退货单);
				po.set客户(rs.getString("f_supplyer"));
				po.set仓库(rs.getString("f_repertory"));
				po.set操作员(rs.getString("f_operator"));
				po.set业务员(rs.getString("f_operator"));
				po.set总额(rs.getLong("f_total"));
				po.set备注(rs.getString("f_remark"));
				//po.setDate(date2);
				List<商品列表单项po> item=new ArrayList<商品列表单项po>();
				int key=rs.getInt("f_item");
				s2=c.createStatement();
				ResultSet rs2=s2.executeQuery("select * from "+book.getName()+"_import_export_item where f_key='"+key+"'");
				while(rs2.next()){
					商品列表单项po _item=new 商品列表单项po();
					_item.set商品编号(rs2.getString("f_commodityID"));
					_item.set商品名称(rs2.getString("f_commodityName"));
					_item.set商品型号(rs2.getString("f_commodityModel"));
					_item.set数量(rs2.getInt("f_commodityNum"));
					_item.set单价(rs2.getLong("f_commodityUnitPrice"));
					_item.set金额(rs2.getLong("f_total"));
					_item.set备注(rs2.getString("f_remark"));
					item.add(_item);
					}
				s2.close();
				po.set入库商品列表(item);
				list.add(po);
		}
			s.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	
	//还未测试
	public List<DocumentPo> get(DocumentPo from, DocumentPo to) {
		//时间，客户，仓库
		List<DocumentPo> list=new ArrayList<DocumentPo>();
		//String operator=from.get操作员();
		String storage=from.get仓库();
		String client=from.get客户();
		String from_date;String to_date;
		from_date=from.getDate()==null? null:new SimpleDateFormat("yyyy-MM-dd").format(from.getDate());
		to_date=to.getDate()==null?null:new SimpleDateFormat("yyyy-MM-dd").format(to.getDate());
		int type=from.isType()? 1:0;             //进货单传递type=false
		Connection c=Database.getConnection();
		Statement s;Statement s2;ResultSet rs = null;
		String query_client=client==null? "":"and f_supplyer like '%"+client+"%'";
		String query_storage=storage==null? "":"and f_repertory like '%"+storage+"%'";
		String query_from=from_date==null? "":"and f_date>='"+from_date+"'";
		String query_to=to_date==null? "":"and f_date<='"+to_date+"'";
		try {
			s = c.createStatement();
			rs=s.executeQuery("select * from "+book.getName()+"_import_export where f_isApproved='"+1+"'"+query_client+query_storage+query_from+query_to+ "and f_type='"+type+"'");//+"and f_date>='"+from_date+"'"+"and f_date<='"+to_date+"'"
		   while(rs.next()){
				进退货单po po=new 进退货单po();
				po.set单据编号(rs.getString("f_id"));
				po.set类型(rs.getInt("f_type")==0? 进货单据类型.进货单:进货单据类型.进货退货单);
				po.set客户(rs.getString("f_supplyer"));
				po.set仓库(rs.getString("f_repertory"));
				po.set操作员(rs.getString("f_operator"));
				po.set业务员(rs.getString("f_operator"));
				po.set总额(rs.getLong("f_total"));
				po.set备注(rs.getString("f_remark"));
				//po.setDate(date2);
				List<商品列表单项po> item=new ArrayList<商品列表单项po>();
				int key=rs.getInt("f_item");
				s2=c.createStatement();
				ResultSet rs2=s2.executeQuery("select * from "+book.getName()+"_import_export_item where f_key='"+key+"'");
				while(rs2.next()){
					商品列表单项po _item=new 商品列表单项po();
					_item.set商品编号(rs2.getString("f_commodityID"));
					_item.set商品名称(rs2.getString("f_commodityName"));
					_item.set商品型号(rs2.getString("f_commodityModel"));
					_item.set数量(rs2.getInt("f_commodityNum"));
					_item.set单价(rs2.getLong("f_commodityUnitPrice"));
					_item.set金额(rs2.getLong("f_total"));
					_item.set备注(rs2.getString("f_remark"));
					item.add(_item);
					}
				s2.close();
				po.set入库商品列表(item);
				list.add(po);
		}
			s.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}


	@Override
	public Po approve(DocumentPo document) {
		String id=((进退货单po) document).get单据编号();
		Po m=new Po();int key = 0;//int totalNum;
		String client = null;long total=0;
		List<String>commodityID=new ArrayList<String>();
		List<Integer>commodityNum=new ArrayList<Integer>();
		List<Long> commodityUnitPrice=new ArrayList<Long>();
		List<Long> commodityTotalPrice=new ArrayList<Long>();
		Connection c=Database.getConnection();
		Statement s;
		try {
			s = c.createStatement();
			ResultSet rs_isApproved=s.executeQuery("select f_isApproved  from "+book.getName()+"_import_export where f_id='"+id+"'");
			while(rs_isApproved.next()){
				if(rs_isApproved.getInt(1)==1){
					m.failed("该单据已审批！");
					return m;
				}
			}
			s.executeUpdate("update "+book.getName()+"_import_export set f_isApproved='"+1+"' where f_id='"+id+"';");
			
			//进货单审批后，增加库存中的数量；退货单审批后，减少库存中的数量;
			ResultSet rs=s.executeQuery("select f_item from "+book.getName()+"_import_export where f_id='"+id+"'");
			while(rs.next()){
				key=rs.getInt("f_item");
			}
		
			ResultSet rs2=s.executeQuery("select f_supplyer from "+book.getName()+"_import_export where f_id='"+id+"';");
			while(rs2.next()){
			 client=rs2.getString(1);
			}
			ResultSet rs1=s.executeQuery("select f_total from "+book.getName()+"_import_export where f_id='"+id+"';");
			while(rs1.next()){
			 total=rs1.getLong("f_total");
			}
			
		
			
			ResultSet rs3=s.executeQuery("select f_commodityID,f_commodityNum,f_commodityUnitPrice,f_total from "+book.getName()+"_import_export_item where f_key='"+key+"'");
			while(rs3.next()){
				commodityID.add(rs3.getString("f_commodityID"));
				commodityNum.add(rs3.getInt("f_commodityNum"));
				commodityUnitPrice.add(rs3.getLong("f_commodityUnitPrice"));
				commodityTotalPrice.add(rs3.getLong("f_total"));
			}
			if(id.contains("JHD")){
				s.execute("update "+book.getName()+"_client set yingfu=(yingfu+"+total+") where name='"+client+"'");
				for(int i=0;i<commodityID.size();i++){
					s.execute("update "+book.getName()+"_commodity set f_stockNum=(f_stockNum+"+commodityNum.get(i)+") where f_commodityID='"+commodityID.get(i)+"'");
					//修改commodity最近一次进价
					s.execute("update "+book.getName()+"_commodity set f_recent_importPrice='"+commodityUnitPrice.get(i)+"' where f_commodityID='"+commodityID.get(i)+"'");
					//修改库存总价和均价
					s.execute("update "+book.getName()+"_commodity set f_totalMoney=(f_totalMoney+"+commodityTotalPrice.get(i)+") where f_commodityID='"+commodityID.get(i)+"'");
					s.execute("update "+book.getName()+"_commodity set f_stockAveragePrice=(f_totalMoney/f_stockNum)"+" where f_commodityID='"+commodityID.get(i)+"'");				
				}
				//添加成本支出
					经营情况DB db=new 经营情况DB(book);
					Po r1=db.add销售成本(total);
					if(!r1.isSuccessed()){
						m.failed("添加商品销售成本失败！");
						return m;
					}
					
			}
			else if(id.contains("JHTHD")){
				@SuppressWarnings("unused")
				long total_TH_stock=0;
				s.execute("update "+book.getName()+"_client set yingshou=(yingshou+"+total+") where name='"+client+"'");
				for(int i=0;i<commodityID.size();i++){
					ResultSet rs5=s.executeQuery("select f_stockAveragePrice from "+book.getName()+"_commodity where f_commodityID='"+commodityID.get(i)+"'");
					while(rs5.next()){
						total_TH_stock+=rs5.getLong(1)*commodityNum.get(i);
					}
					s.execute("update "+book.getName()+"_commodity set f_stockNum=(f_stockNum-"+commodityNum.get(i)+") where f_commodityID='"+commodityID.get(i)+"'");
					s.execute("update "+book.getName()+"_commodity set f_totalMoney=(f_totalMoney-f_stockAveragePrice*"+commodityNum.get(i)+") where f_commodityID='"+commodityID.get(i)+"'");
					s.execute("update "+book.getName()+"_commodity set f_stockAveragePrice=(case when f_stockNum=0 then 0 else f_totalMoney/f_stockNum end)"+" where f_commodityID='"+commodityID.get(i)+"'");		
					
				}
				//添加进货退货差价收入
				/*	经营情况DB db=new 经营情况DB(book);
					Po r1=db.add进货退货差价收入(total-total_TH_stock);
					if(!r1.isSuccessed()){
						m.failed("添加商品销售成本失败！");
						return m;
					}*/
					
				
			}
			
			s.close();
			m.successed();
		} catch (SQLException e) {
			m.failed(e.getMessage());
			e.printStackTrace();
		}
		return m;
	}





	
	
	
}
